from django.shortcuts import render
from django.http import HttpResponse
from .models import Book,BookOrder,Author,Publisher

from django.db.models import F,Q,Count,Sum,Avg
from django.db import connection


# Create your views here.

def index(request):
    print(type(Book.objects)) #django.db.models.manager.Manager
    return HttpResponse("index")

def index2(request):
    # books = Book.objects.filter(id__gte=2).exclude(id=3)
    # for book in  books:
    #     print(book.id)
    books = Book.objects.annotate(author_name=F("author__name"))
    for book in books:
        print("%s/%s" % (book.name,book.author_name))
    return HttpResponse("index2")

def index3(request):
    #orders = BookOrder.objects.order_by("create_time")
    orders = BookOrder.objects.order_by("-create_time","price")
    for order in orders:
        print("%s/%s" % (order.id,order.create_time))
    return HttpResponse("index3")

def index4(request):
    #如果models中 加了related_name  order_nums=Count("你的名称__id")
    #orders = Book.objects.annotate(order_nums=Count("bookorder")).order_by("-order_nums")
    orders = Book.objects.annotate(order_nums=Count("bookorder")).order_by("-order_nums")
    for order in orders:
        print("%s/%s" % (order.name,order.order_nums))
    return HttpResponse("index4")

def index5(request):
    #books = Book.objects.values("id","name",authors=F("author__name"))
    #F表达式在这里主要是为了减少MySQL的查询  降低损耗
    #books = Book.objects.values("id", "name", order_nums=Count("bookorder"))
    books = Book.objects.values_list("id",flat=True)
    #values_list中只有一个字段。那么你可以传递flat=True来将结果扁平化  也就是说
    #从 [(1),(2)] => [1,2]
    for book in books:
        print(book)
    return HttpResponse("index5")


def index6(request):
    orders = Book.objects.select_related("author","publisher")
    #从book表中查询出所有的内容  这个时候 你需要获取作者名称  这时候需要重新到 作者表中在查询一次
    #用了 select_related 我们就不需要了
    for order in orders:
        print(order.author.name)
        print(order.publisher.name)
    print(connection.queries)
    return HttpResponse("index6")


def index7(request):
    books = Book.objects.prefetch_related("bookorder_set")
    for book in books:
        print("="*50)
        print(book.name)
        orders = book.bookorder_set.all()
        for order in  orders:
            print(order.id)
        print("="*50)
    print(connection.queries)
    return HttpResponse("index7")

def index8(request):
    books = Book.objects.defer('name')
    #不打印book.name
    #就不会再额外的查询一次
    #原本:{'sql': 'SELECT `book`.`id`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id` FROM `book`'
    for book in books:
        print(book.name) #如果 获取book.name
        #会再次查询一次 name
        print(type(book))
    print(connection.queries)
    return HttpResponse("index8")

def index9(request):
    books = Book.objects.only('name')
    #不打印book.name
    #就不会再额外的查询一次
    #原本:{'sql': 'SELECT `book`.`id`, `book`.`pages`, `book`.`price`, `book`.`rating`, `book`.`author_id`, `book`.`publisher_id` FROM `book`'
    for book in books:
        print(book.name) #如果 获取book.name
        #会再次查询一次 name
        print(type(book))
    print(connection.queries)
    return HttpResponse("index9")

def index10(request):
    #publisher = Publisher.objects.create(name="91wangfan出版社")
    # publisher = Publisher.objects.get_or_create(name="91p出版社")
    publisher = Publisher.objects.bulk_create([
        Publisher(name="kangbazi"),
        Publisher(name="kangbazi666"),
        Publisher(name="kangbazi888"),
        ]
    )

    return HttpResponse("index10")

def index11(request):
    # books = Book.objects.count()
    result = Book.objects.filter(name='afdasfsdf').exists()
    if result:
        print("用户已经存在")
    else:
        Book.objects.create(name="金什么梅",pages=66,price=12.3,rating=8.9,author_id=1,publisher_id=5)
    print(connection.queries)
    return HttpResponse("index11")